import requests
import json
import time
import pymysql
import traceback
from selenium.webdriver import Chrome, ChromeOptions

# 代码最新更新时间 2021.06

def get_data():
    """
    :return: 爬取疫情相关的历史数据、当日详细数据、全国整体数据
    """

    # url_1包括：1.lastUpdateTime：最新更新时间  2.chinaTotal：全国疫情总数据  3.chinaAdd：全国新增数据  4.areaTree：全球疫情数据
    # url_2包括：1.chinaDayList：全国历史数据  2.chinaDayAddList：历史新增数据
    url_1 = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"
    url_2 = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_other"

    # 基本的反爬措施
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/91.0.4472.124 Safari/537.36 Edg/91.0.864.64'
    }

    r1 = requests.get(url_1, headers=headers)
    r2 = requests.get(url_2, headers=headers)

    # json字符串转字典，便于储存和访问
    res1 = json.loads(r1.text)
    res2 = json.loads(r2.text)
    data_1 = json.loads(res1["data"])
    data_2 = json.loads(res2["data"])

    history = dict()  # 定义字典，用于存储历史数据并返回
    for item in data_2["chinaDayList"]:
        date = item['y'] + '.' + item["date"]  # 年份+具体月.日
        struct_time = time.strptime(date, "%Y.%m.%d")  # 改变时间格式，避免插入数据库时报错，转换为datetime类型
        str_time = time.strftime("%Y-%m-%d", struct_time)  # 改成2021-01-01的形式
        confirm = item["confirm"]  # 历史确诊人数
        suspect = item["suspect"]  # 历史疑似人数
        heal = item["heal"]  # 历史治愈人数
        dead = item["dead"]  # 历史死亡人数
        history[str_time] = {"confirm": confirm, "suspect": suspect, "heal": heal, "dead": dead}  # 传入字典中保存

    # 历史新增数据
    for item_add in data_2["chinaDayAddList"]:
        date = item_add['y'] + '.' + item_add["date"]  # 年份+具体月.日
        struct_time = time.strptime(date, "%Y.%m.%d")
        str_time = time.strftime("%Y-%m-%d", struct_time)
        confirm = item_add["confirm"]  # 历史新增确诊人数
        suspect = item_add["suspect"]  # 历史新增疑似人数
        heal = item_add["heal"]  # 历史新增治愈人数
        dead = item_add["dead"]  # 历史新增死亡人数
        history[str_time].update({"confirm_add": confirm, "suspect_add": suspect, "heal_add": heal, "dead_add": dead})

    details = []  # 定义列表，存储当日详细数据
    update_time = data_1["lastUpdateTime"]  # 数据最新更新时间
    data_province = data_1["areaTree"][0]["children"]  # 全国各省份详细数据
    for pro_info in data_province:
        province = pro_info["name"]  # 省名
        for city_info in pro_info["children"]:
            city = city_info["name"]   # 市名
            confirm = city_info["total"]["confirm"]  # 当日确诊人数
            suspect = city_info["total"]["suspect"]  # 当日疑似人数
            heal = city_info["total"]["heal"]  # 当日治愈人数
            dead = city_info["total"]["dead"]  # 当日死亡人数
            details.append([update_time, province, city, confirm, suspect, heal, dead])

    total = []  # 定义列表，存储全国累计实时数据
    confirm_total = data_1["chinaTotal"]['confirm']
    suspect_total = data_1["chinaTotal"]['suspect']
    heal_total = data_1["chinaTotal"]['heal']
    dead_total = data_1["chinaTotal"]['dead']
    total.append([update_time, confirm_total, suspect_total, heal_total, dead_total])

    return history, details, total


def get_conn():
    """
    :return: 连接MySQL
    """
    conn = pymysql.connect(host="****",  # 本地ip或你的域名地址
                           user="****",  # MySQL登录用户名
                           password="****",  # MySQL登录密码
                           db="****",  # 储存数据的数据库名
                           charset="utf8")
    # conn表示连接数据库，对数据的操作需要通过cursor来实现
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    """
    :param conn:
    :param cursor:
    :return: 关闭MySQL
    """
    if cursor:
        cursor.close()
    if conn:
        conn.close()


def update_total():
    """
    :return: 更新全国累计确诊、累计疑似、累计治愈、累计死亡数据
    """
    cursor = None
    conn = None
    try:
        total = get_data()[2]  # 通过get_data函数获取全国整体数据
        conn, cursor = get_conn()  # 连接Mysql数据库
        sql = "insert into total(update_time, confirm_total, suspect_total, heal_total, dead_total)" \
              "values(%s,%s,%s,%s,%s)"
        sql_query = "select update_time from total where update_time=%s"
        if not cursor.execute(sql_query, total[0][0]):
            cursor.execute(sql, total[0])
            conn.commit()
            print('%s更新到最新全国累计数据!' % time.strftime("%Y-%m-%d %H:%M:%S"))
        else:
            print('%s已是最新全国累计数据!' % time.strftime("%Y-%m-%d %H:%M:%S"))
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def update_details():
    """
    :return: 更新当日详细数据
    """
    cursor = None
    conn = None
    try:
        details = get_data()[1]  # 通过get_data函数获取当日详细数据
        conn, cursor = get_conn()
        sql = "insert into details(update_time, province, city, confirm, suspect, heal, dead)" \
              "values(%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select update_time from details order by update_time desc limit 1)'  # 对比当前最新的时间戳
        cursor.execute(sql_query, details[0][0])  # details[0][0]把数据传入到sql_query语句中的%s处，判断时间是否相等，来看是否更新
        if not cursor.fetchone()[0]:  # fetchone函数的返回值是单个的元组,也就是一行记录,如果上述时间不等，就是None，那么就需要更新
            print('%s开始更新当日详细数据!' % time.strftime("%Y-%m-%d %H:%M:%S"))
            for item in details:
                cursor.execute(sql, item)
            conn.commit()
            print('%s更新到最新当日详细数据!' % time.strftime("%Y-%m-%d %H:%M:%S"))
        else:  # 如果时间相等，就不需要更新
            print('%s已是最新当日详细数据!' % time.strftime("%Y-%m-%d %H:%M:%S"))
    except:
        traceback.print_exc()  # 如果执行失败，打印错误信息
    finally:
        close_conn(conn, cursor)


def update_history():
    """
    更新历史数据
    :return:
    """
    cursor = None
    conn = None
    try:
        history = get_data()[0]
        print('%s开始更新历史数据!' % time.strftime("%Y-%m-%d %H:%M:%S"))
        conn, cursor = get_conn()
        sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select date from history where date=%s"
        for k, v in history.items():
            if not cursor.execute(sql_query, k):
                cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"), v.get("suspect"),
                               v.get("suspect_add"), v.get("heal"), v.get("heal_add"),
                               v.get("dead"), v.get("dead_add")])
        conn.commit()
        print('%s历史数据更新完毕!' % time.strftime("%Y-%m-%d %H:%M:%S"))
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def get_hot_search():
    """
    爬取热搜数据
    :return:
    """

    option = ChromeOptions()
    option.add_argument("--headless")  # 隐藏游览器
    option.add_argument("--no--sandbox")  # linux系统
    browser = Chrome(options=option, executable_path='chromedriver.exe')
    url = 'https://top.baidu.com/board?tab=realtime'
    browser.get(url)
    content = browser.find_elements_by_xpath('//*[@id="sanRoot"]/main/div[2]/div/div[2]/div/div[2]/a')  # 热搜内容
    hot_index = browser.find_elements_by_xpath('//*[@id="sanRoot"]/main/div[2]/div/div[2]/div/div[1]/div[2]')  # 热搜指数
    hot_search = zip([item.text.split()[0] for item in content], [item.text for item in hot_index])
    return hot_search


def update_hot_search():
    """
    将热搜插数据入数据库
    :return:
    """
    cursor = None
    conn = None
    try:
        hot_search = get_hot_search()
        print('%s开始更新热搜数据!' % time.strftime("%Y-%m-%d %H:%M:%S"))
        conn, cursor = get_conn()
        sql = "insert into hotsearch(dt, content, hot_index) values(%s, %s, %s)"
        ts = time.strftime("%Y-%m-%d %X")
        for item in hot_search:
            cursor.execute(sql, (ts, item[0], item[1]))
        conn.commit()
        print('%s热搜数据更新完毕!' % time.strftime("%Y-%m-%d %H:%M:%S"))
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


if __name__ == '__main__':
    update_details()
    update_history()
    update_total()
    get_hot_search()
    update_hot_search()
